//	Company:	HTHK
//	NAME:	    JobReportSearch.java
//	TITLE:	    The maintence of ProjectCategory in Time Sheet System
//	FUNCTION:	The flow control interface of class ProjectCategory associate with the table
//	BY:	        Mike Cheng
//	DATE:	    14/1/2003
// --------------------------------------------------------------
// MODIFICATION HISTORY
// --------------------------------------------------------------
//	CR Log #:  99999  BY:  XXXX   DATE:  dd/mm/yyyy
//	ERROR:	XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
//	FIX:	XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
// --------------------------------------------------------------
package tss.jobreport;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;

import tss.common.DBServlet2;

import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

public class JobReportSearch extends DBServlet2 {
	private Logger logger = Logger.getLogger(JobReportSearch.class);

	@Override
	protected String perform(HttpServletRequest request,
			HttpServletResponse response, StrutsMessage message) {
		HttpSession session = request.getSession();
		Vector jobvec = new Vector();// ---------------------------------------
		String companyCodeStr = "";

		String p_fromYear, p_fromMonth, p_toYear, p_toMonth;
		String p_fromDay, p_toDay;
		// p_fromYear = request.getParameter("year_from");
		// p_fromMonth = request.getParameter("month_from");
		// p_toYear = request.getParameter("year_to");
		// p_toMonth = request.getParameter("month_to");
		// session.setAttribute("year_from",p_fromYear);
		// session.setAttribute("month_from",p_fromMonth);
		// session.setAttribute("year_to",p_toYear);
		// session.setAttribute("month_to",p_toMonth);
		String search_date = request.getParameter("selected_date");
		String search_date1 = request.getParameter("selected_date1");
		session.setAttribute("date_from", search_date);
		session.setAttribute("date_to", search_date1);
		p_fromYear = search_date.substring(0, 4);
		p_toYear = search_date1.substring(0, 4);

		p_fromMonth = search_date.substring(5, 7);
		p_toMonth = search_date1.substring(5, 7);

		p_fromDay = search_date.substring(8, 10);
		p_toDay = search_date1.substring(8, 10);

		session.setAttribute("year_from", p_fromYear);
		session.setAttribute("month_from", p_fromMonth);
		session.setAttribute("year_to", p_toYear);
		session.setAttribute("month_to", p_toMonth);
		session.setAttribute("day_from", p_fromDay);
		session.setAttribute("day_to", p_toDay);

		if (request.getParameter("company_code") != null) {
			companyCodeStr = request.getParameter("company_code");
			session.setAttribute("company", companyCodeStr);
		}

		// Added by Mike. 2004-01-14 .
		int i = 0;
		int j = 0;
		int m = Integer.parseInt(p_toYear);
		int n = Integer.parseInt(p_fromYear);
		int k = Integer.parseInt(p_toMonth);
		int l = Integer.parseInt(p_fromMonth);
		j = m - n;
		i = j * 12 + (k - l) + 1;

		int[] mon = new int[i];
		int[] year = new int[i];
		mon[0] = l;
		year[0] = n;
		for (int a = 1; a < i; a++) {
			mon[a] = mon[a - 1] + 1;
			if (mon[a - 1] == 12) {
				mon[a] = 1;
				year[a] = year[a - 1] + 1;
			} else
				year[a] = year[a - 1];

		}
		String[] Date = new String[i];
		for (int s = 0; s < i; s++) {
			String mm = "";
			if (mon[s] < 10) {
				mm = "0" + (new Integer(mon[s])).toString();
			} else {
				mm = (new Integer(mon[s])).toString();
			}

			Date[s] = (new Integer(year[s])).toString() + "/" + mm;
		}

		Vector year_month = new Vector();

		for (int count = 0; count < i; count++) {
			String jobsql = " SELECT A.PROJECT_NAME PROJECT_NAME, A.PROJECT_DESC PROJECT_DESC, SUM(A.SPEND) HOURS ";
			jobsql += " FROM TSS_V_PROJECT_REPORT A, TSS.TSS_C_PROJECT B, TSS.TSS_C_PROJECT_CATEGORY C ";
			jobsql += " WHERE A.PROJECT_NAME=B.PROJECT_NAME ";
			jobsql += " AND B.PROJECT_CATEGORY_NAME=C.PROJECT_CATEGORY_NAME ";
			jobsql += " AND C.COMPANY_CODE='" + companyCodeStr + "' ";
			jobsql += " AND TO_CHAR(A.SPEND_DATE, 'YYYY/MM/DD') >= '"
					+ p_fromYear + "/" + p_fromMonth + "' ";
			jobsql += " AND TO_CHAR(A.SPEND_DATE, 'YYYY/MM') <= '" + p_toYear
					+ "/" + p_toMonth + "'";
			jobsql += " AND TO_CHAR(A.SPEND_DATE, 'YYYY/MM') = '" + Date[count]
					+ "'";
			if (count == 0) {
				jobsql += " AND TO_CHAR(A.SPEND_DATE, 'DD') >= '" + p_fromDay
						+ "' ";
			}
			if (count == i - 1) {
				jobsql += " AND TO_CHAR(A.SPEND_DATE, 'DD') <='" + p_toDay
						+ "' ";
			}
			jobsql += " GROUP BY A.PROJECT_NAME, A.PROJECT_DESC ";
			jobsql += " ORDER BY A.PROJECT_NAME ";
			logger.debug(jobsql);

			ReportContainer joblist = new ReportContainer();
			float total = 0f;

			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				con = this.getConnection();
				pstmt = con.prepareStatement(jobsql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				rs = pstmt.executeQuery();
				Vector projects = new Vector();
				while (rs.next())
					total += rs.getFloat(3);
				rs.beforeFirst();
				while (rs.next()) {
					String project[] = new String[4];
					project[0] = rs.getString(1);
					project[1] = (rs.getString(2) != null && rs.getString(2)
							.length() != 0) ? rs.getString(2) : " ";
					project[2] = rs.getString(3);
					if (total != 0f) {
						project[3] = String.valueOf(Math.round(rs.getFloat(3)
								* 10000f / total) / 100f);
					} else {
						project[3] = "0.00";
					}
					projects.addElement(project);
				}
				if (!projects.isEmpty()) {
					String project[] = new String[4];
					project[0] = "<b>Total</b>";
					project[1] = "&nbsp;";
					project[2] = "" + total;
					project[3] = "100";
					projects.addElement(project);
					logger.debug(project[0] + "aaaaaaaaaaaaaaa");

					joblist.setCompany(companyCodeStr);
					joblist.setProjects(projects);
					jobvec.addElement(joblist);

					String[] str_year_month = new String[3];
					str_year_month[0] = Date[count];
					if (count == 0) {
						str_year_month[1] = p_fromDay;
					} else {
						str_year_month[1] = "01";
					}
					if (count == i - 1) {
						str_year_month[2] = p_toDay;
					} else {
						int yearInt = Integer.parseInt(Date[count].substring(0,
								4));
						int monthInt = Integer.parseInt(Date[count].substring(
								5, 7));
						if (monthInt == 4 || monthInt == 6 || monthInt == 9
								|| monthInt == 11) {
							str_year_month[2] = "30";
						} else if (monthInt == 2) {
							if (yearInt % 4 == 0
									&& (yearInt % 100 != 0 || yearInt % 400 == 0))
								str_year_month[2] = "29";
							else
								str_year_month[2] = "28";
						} else {
							str_year_month[2] = "31";
						}
					}
					year_month.addElement(str_year_month);
				}
			} catch (Exception e) {
				logger.error(e,e);
			} finally {
			/*	if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						logger.error(e,e);
					}
				}
				if (pstmt != null) {
					try {
						pstmt.close();
					} catch (SQLException e) {
						logger.error(e,e);
					}
				}*/
				// IISZ added in 2013-07-11 begin
				JdbcUtil.release(pstmt, rs);
				// IISZ added in 2013-07-11 end
			}
		}
		if (jobvec.isEmpty()) {
			message.setMessage("No record, please input another criteria.");
			return "tss/jobreport/JobExport.jsp";

		} else {
			request.setAttribute("year_month", year_month);
			request.setAttribute("jobvec", jobvec);
			return "tss/jobreport/JobReport.jsp";
		}

	}
}
